package com.spoto.oa.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.spoto.oa.dao.EmployeeDAO;
import com.spoto.oa.model.Employee;
import com.spoto.oa.util.DB;

/**
 * 
 * @Project_name: OA
 * @Description: 员工信息表数据库操作DAO层的具体实现
 * @Author: LinYiSong
 * @Version:
 * @Date: 2010-9-23~2010-9-23
 */
public class EmployeeDAOImpl implements EmployeeDAO {

	/**
	 * 模糊查找
	 */
	public List<Employee> selectByUsername(String username) {
		username = "%" + username + "%";

		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee  where username like ?");
			pstm.setString(1, username);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public void save(Employee employee) throws SQLException {

		PreparedStatement pstm = DB
				.getConn()
				.prepareStatement(
						"insert into employee(username,password,isAdmin,age,name,sex,birth,"
								+ "dateIn,qq,msn,cell,school,email,phone,address,self) "
								+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		pstm.setString(1, employee.getUsername());
		pstm.setString(2, "888888");
		pstm.setString(3, employee.getIsAdmin());
		pstm.setInt(4, employee.getAge());
		pstm.setString(5, employee.getName());
		pstm.setString(6, employee.getSex());
		pstm.setDate(7, employee.getBirth());
		pstm.setDate(8, employee.getDateIn());
		pstm.setString(9, employee.getQq());
		pstm.setString(10, employee.getMsn());
		pstm.setString(11, employee.getCell());
		pstm.setString(12, employee.getSchool());
		pstm.setString(13, employee.getEmail());
		pstm.setString(14, employee.getPhone());
		pstm.setString(15, employee.getAddress());
		pstm.setString(16, employee.getSelf());

		pstm.executeUpdate();

	}

	/**
	 * 通过用户姓名搜索用户
	 */
	public List<Employee> selectByName(String name) {
		name = "%" + name + "%";
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee  where name like ?");
			pstm.setString(1, name);
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	/**
	 * 将ResutlSet封装成List
	 * 
	 * @param rs
	 * @return
	 */
	private List<Employee> resultSetToList(ResultSet rs) {
		List<Employee> employees = new ArrayList<Employee>();
		try {
			while (rs.next()) {
				Employee employee = new Employee();
				employee.setId(rs.getInt("id"));
				employee.setUsername(rs.getString("username"));
				employee.setPassword(rs.getString("password"));
				employee.setIsAdmin(rs.getString("isAdmin"));
				employee.setAge(rs.getInt("age"));
				employee.setName(rs.getString("name"));
				employee.setSex(rs.getString("sex"));
				employee.setBirth(rs.getDate("birth"));
				employee.setDateIn(rs.getDate("dateIn"));
				employee.setQq(rs.getString("qq"));
				employee.setMsn(rs.getString("msn"));
				employee.setCell(rs.getString("cell"));
				employee.setSchool(rs.getString("school"));
				employee.setEmail(rs.getString("email"));
				employee.setPhone(rs.getString("phone"));
				employee.setAddress(rs.getString("address"));
				employee.setSelf(rs.getString("self"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return employees;

	}

	/**
	 * 删出员工
	 */
	public void delete(Employee employee) {
		String username = employee.getUsername();
		PreparedStatement pstm;
		try {
			pstm = DB.getConn().prepareStatement(
					"delete from employee where username= ?");
			pstm.setString(1, username);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 更新员工信息
	 */
	public void update(Employee employee) {
		try {

			PreparedStatement pstm = DB
					.getConn()
					.prepareStatement(
							"update  employee set isAdmin=?,age=?,name=?,sex=?,birth=?,"
									+ "dateIn=?,qq=?,msn=?,cell=?,school=?,email=?,phone=?,address=?,self=? where username=?");

			pstm.setString(1, employee.getIsAdmin());
			pstm.setInt(2, employee.getAge());
			pstm.setString(3, employee.getName());
			pstm.setString(4, employee.getSex());
			pstm.setDate(5, employee.getBirth());
			pstm.setDate(6, employee.getDateIn());
			pstm.setString(7, employee.getQq());
			pstm.setString(8, employee.getMsn());
			pstm.setString(9, employee.getCell());
			pstm.setString(10, employee.getSchool());
			pstm.setString(11, employee.getEmail());
			pstm.setString(12, employee.getPhone());
			pstm.setString(13, employee.getAddress());
			pstm.setString(14, employee.getSelf());
			pstm.setString(15, employee.getUsername());

			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据用户名找出与其匹配的员工信息
	 */
	public Employee getByUsername(String username) {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee  where username=?");
			//搜索时,信息为空时,表示全部
			if (username.equals("")) {
				pstm = DB.getConn().prepareStatement("select * from employee");
			} else {
				pstm.setString(1, username);
			}
			rs = pstm.executeQuery();
			List<Employee> employees = resultSetToList(rs);
			return employees.get(0);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}

		return null;
	}

	/**
	 * 取得所有的信息
	 */
	public List<Employee> getAll() {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement("select * from employee");
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	
	/**
	 * 存导入数据时发生冲突的数据
	 */
	public void saveConfict(Employee employee) throws SQLException {
		PreparedStatement pstm = DB
				.getConn()
				.prepareStatement(
						"insert into employee_conflict(username,password,isAdmin,age,name,sex,birth,"
								+ "dateIn,qq,msn,cell,school,email,phone,address,self) "
								+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		pstm.setString(1, employee.getUsername());
		pstm.setString(2, "888888");
		pstm.setString(3, employee.getIsAdmin());
		pstm.setInt(4, employee.getAge());
		pstm.setString(5, employee.getName());
		pstm.setString(6, employee.getSex());
		pstm.setDate(7, employee.getBirth());
		pstm.setDate(8, employee.getDateIn());
		pstm.setString(9, employee.getQq());
		pstm.setString(10, employee.getMsn());
		pstm.setString(11, employee.getCell());
		pstm.setString(12, employee.getSchool());
		pstm.setString(13, employee.getEmail());
		pstm.setString(14, employee.getPhone());
		pstm.setString(15, employee.getAddress());
		pstm.setString(16, employee.getSelf());

		pstm.executeUpdate();

	}

	/**
	 * 取得冲突的数据
	 */
	public Employee getByUsernameConfict(String username) {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee_conflict  where username=?");
			pstm.setString(1, username);
			rs = pstm.executeQuery();
			List<Employee> employees = resultSetToList(rs);
			return employees.get(0);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}

		return null;
	}

	/**
	 * 删除冲突的员工数据
	 */
	public void deleteConfict(Employee employee) {
		String username = employee.getUsername();
		PreparedStatement pstm;
		try {
			pstm = DB.getConn().prepareStatement(
					"delete from employee_conflict where username= ?");
			pstm.setString(1, username);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/**
	 * 取得所有冲突的信息
	 * @return
	 */
	public List<Employee> getAllConfict() {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement("select * from employee_conflict");
			rs = pstm.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSetToList(rs);
	}

	public void deleteByUsername(String username) {
		PreparedStatement pstm;
			try {
				pstm = DB.getConn().prepareStatement(
						"delete from employee  where username=?");
				pstm.setString(1, username);
				pstm.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			


		
	}
	/**
	 * 根据qq模糊查询
	 * @param qq
	 * @return
	 */
	public List<Employee> getByQq(String qq) {
		System.out.println(qq);
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee  where qq like ?");
			pstm.setString(1, "%"+qq+"%");
			rs = pstm.executeQuery();
			return resultSetToList(rs);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}

		return null;
	}
	/**
	 * 根据手机号模糊查询
	 * @param cell
	 * @return
	 */
	public List<Employee> getAllByCell(String cell) {
		PreparedStatement pstm;
		ResultSet rs = null;
		try {
			pstm = DB.getConn().prepareStatement(
					"select * from employee where cell like ?");
			pstm.setString(1, "%"+cell+"%");
			rs = pstm.executeQuery();
			return resultSetToList(rs);

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (java.lang.IndexOutOfBoundsException e) {
			return null;
		}

		return null;
	}

	public void updatePsw(String username, String psw) {
		PreparedStatement pstm;
		try {
			pstm = DB
			.getConn()
			.prepareStatement(
					"update  employee set password=? where username=?");
			pstm.setString(1, psw);
			pstm.setString(2, username);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
